package br.com.skytef.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import br.com.skytef.jdbc.ConnectionFactory;

public class ChartDao {

	public Map<String, Double> getSalesMonth(int year) {
		return this.execSelect(this.getQrySaleMonth(year));
	}

	private Map<String, Double> execSelect(String qry) {
		Connection connection = new ConnectionFactory().getConnection();
		Map<String, Double> data = new HashMap<String, Double>();

		try {
			PreparedStatement stmt = connection.prepareStatement(qry);
			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				data.put(rs.getString(1), rs.getDouble(2));
			}

			connection.close();
			stmt.close();
			rs.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		return data;
	}

	private String getQrySaleMonth(int year) {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT Month(lista.datacriacao), ");
		sql.append("       Sum(lista.quant * lista.valorunit)  ");
		sql.append("FROM  (SELECT ped.idpedido,  ");
		sql.append("              ped.datacriacao,  ");
		sql.append("              itens.quant,  ");
		sql.append("              itens.valorunit  ");
		sql.append("       FROM   pedidos ped  ");
		sql.append("              LEFT JOIN pedidos_has_produtos itens  ");
		sql.append("                     ON ped.idpedido = itens.pedidos_idpedido  ");
		sql.append("       WHERE  ped.cancelado = false  ");
		sql.append("              AND Year(ped.datacriacao) = " + year + ") AS lista  ");
		sql.append("GROUP  BY Month(datacriacao)  ");

		return sql.toString();

	}

}
